﻿using System.Globalization;
using System.IO;
using System.Web.UI.HtmlControls;
using H2.NET.Common;
using H2.NET.Data.Common;
using H2.NET.Web.Management.Config.Service;
using H2.NET.Web.Pages.SMD.Model;
using H2.NET.Web.Workflow.DwzCode;
using System;
using System.Collections.Generic;
using NPOI.HSSF.UserModel;

namespace H2.NET.Web.Pages.SMD
{
    public partial class StoreReport : BasePage
    {
        private readonly ConfigService _configService = new ConfigService();
        public bool HasValue = false;
        protected void Page_Load(object sender, EventArgs e)
        {
            var action = AksRequest.GetQueryString("action");
            if (action == "save")
            {
                SaveSummary();
                return;
            }
            if (action == "reset")
            {
                var year = Utils.StrToInt(AksRequest.GetFormString("year"), 0);
                (t_smd_Summary._Year == year & t_smd_Summary._ReportType == "main").Delete<t_smd_Summary>(
                Storage.Context3);
                SummaryData();
            }
            if (action == "export")
            {
                Export();
            }

            SummaryData();
        }

        protected void SummaryData()
        {
            var year = Utils.StrToInt(AksRequest.GetFormString("year"), DateTime.Now.Year);
            clsCmm.clsDB.Storage("smdDB");
            yearSearch.Value = year.ToString(CultureInfo.InvariantCulture);
            var summarys = clsCmm.clsDB.Query("select * from t_smd_Summary where year=" + year + " and ReportType='main'");
            if (summarys == null || summarys.Rows.Count == 0)
            {
                var sql =
                    "SELECT * FROM ( SELECT  COUNT (1) AS ddcn_East FROM  t_smd_Store s WHERE  1 = 1 AND s.StoreType = 'DDCN' AND Region = 'east' AND s.StoreStatus = '营业' AND s.IsRenew = 0 AND YEAR (s.CurrentStartDate) = YEAR (GETDATE()) ) AS a, ( SELECT  COUNT (1) AS ddcn_South FROM  t_smd_Store s WHERE  1 = 1 AND s.StoreType = 'DDCN' AND Region = 'SOUTH' AND s.StoreStatus = '营业' AND s.IsRenew = 0 AND YEAR (s.CurrentStartDate) = YEAR (GETDATE()) ) AS b, ( SELECT  COUNT (1) AS ddcn_West FROM  t_smd_Store s WHERE  1 = 1 AND s.StoreType = 'DDCN' AND Region = 'WEST' AND s.StoreStatus = '营业' AND s.IsRenew = 0 AND YEAR (s.CurrentStartDate) = YEAR (GETDATE()) ) AS c, ( SELECT  COUNT (1) AS ddcn_North FROM  t_smd_Store s WHERE  1 = 1 AND s.StoreType = 'DDCN' AND Region = 'NORTH' AND s.StoreStatus = '营业' AND s.IsRenew = 0 AND YEAR (s.CurrentStartDate) = YEAR (GETDATE()) ) AS d, ( SELECT  COUNT (1) AS ddcn_All FROM  t_smd_Store s WHERE  1 = 1 AND s.StoreType = 'DDCN' AND s.StoreStatus = '营业' AND s.IsRenew = 0 AND YEAR (s.CurrentStartDate) = YEAR (GETDATE()) ) AS e, ( SELECT  COUNT (*) AS ddcn_East_r FROM  t_smd_Store s WHERE  s.StoreType = 'DDCN' AND s.StoreStatus <> '关闭' AND Region = 'EAST' AND s.IsRenew = 1 AND YEAR (s.LastRenewDate) = YEAR (GETDATE()) ) AS f, ( SELECT  COUNT (*) AS ddcn_South_r FROM  t_smd_Store s WHERE  s.StoreType = 'DDCN' AND s.StoreStatus <> '关闭' AND Region = 'SOUTH' AND s.IsRenew = 1 AND YEAR (s.LastRenewDate) = YEAR (GETDATE()) ) AS g, ( SELECT  COUNT (*) AS ddcn_West_r FROM  t_smd_Store s WHERE  s.StoreType = 'DDCN' AND s.StoreStatus <> '关闭' AND Region = 'WEST' AND s.IsRenew = 1 AND YEAR (s.LastRenewDate) = YEAR (GETDATE()) ) AS h, ( SELECT  COUNT (*) AS ddcn_North_r FROM  t_smd_Store s WHERE  s.StoreType = 'DDCN' AND s.StoreStatus <> '关闭' AND Region = 'NORTH' AND s.IsRenew = 1 AND YEAR (s.LastRenewDate) = YEAR (GETDATE()) ) AS i, ( SELECT  COUNT (*) AS ddcn_All_r FROM  t_smd_Store s WHERE  s.StoreType = 'DDCN' AND s.StoreStatus <> '关闭' AND s.IsRenew = 1 AND YEAR (s.LastRenewDate) = YEAR (GETDATE()) ) AS j, ( SELECT  COUNT (1) AS dcss_East FROM  t_smd_Store s WHERE  1 = 1 AND s.StoreType = 'DCSS' AND Region = 'east' AND s.StoreStatus = '营业' AND s.IsRenew = 0 AND YEAR (s.CurrentStartDate) = YEAR (GETDATE()) ) AS ar, ( SELECT  COUNT (1) AS dcss_South FROM  t_smd_Store s WHERE  1 = 1 AND s.StoreType = 'DCSS' AND Region = 'SOUTH' AND s.StoreStatus = '营业' AND s.IsRenew = 0 AND YEAR (s.CurrentStartDate) = YEAR (GETDATE()) ) AS br, ( SELECT  COUNT (1) AS dcss_West FROM  t_smd_Store s WHERE  1 = 1 AND s.StoreType = 'DCSS' AND Region = 'WEST' AND s.StoreStatus = '营业' AND s.IsRenew = 0 AND YEAR (s.CurrentStartDate) = YEAR (GETDATE()) ) AS cr, ( SELECT  COUNT (1) AS dcss_North FROM  t_smd_Store s WHERE  1 = 1 AND s.StoreType = 'DCSS' AND Region = 'NORTH' AND s.StoreStatus = '营业' AND s.IsRenew = 0 AND YEAR (s.CurrentStartDate) = YEAR (GETDATE()) ) AS dr, ( SELECT  COUNT (1) AS dcss_All FROM  t_smd_Store s WHERE  1 = 1 AND s.StoreType = 'DCSS' AND s.StoreStatus = '营业' AND s.IsRenew = 0 AND YEAR (s.CurrentStartDate) = YEAR (GETDATE()) ) AS er, ( SELECT  COUNT (*) AS dcss_East_r FROM  t_smd_Store s WHERE  s.StoreType = 'DCSS' AND s.StoreStatus <> '关闭' AND Region = 'EAST' AND s.IsRenew = 1 AND YEAR (s.LastRenewDate) = YEAR (GETDATE()) ) AS fr, ( SELECT  COUNT (*) AS dcss_South_r FROM  t_smd_Store s WHERE  s.StoreType = 'DCSS' AND s.StoreStatus <> '关闭' AND Region = 'SOUTH' AND s.IsRenew = 1 AND YEAR (s.LastRenewDate) = YEAR (GETDATE()) ) AS gr, ( SELECT  COUNT (*) AS dcss_West_r FROM  t_smd_Store s WHERE  s.StoreType = 'DCSS' AND s.StoreStatus <> '关闭' AND Region = 'WEST' AND s.IsRenew = 1 AND YEAR (s.LastRenewDate) = YEAR (GETDATE()) ) AS hr, ( SELECT  COUNT (*) AS dcss_North_r FROM  t_smd_Store s WHERE  s.StoreType = 'DCSS' AND s.StoreStatus <> '关闭' AND Region = 'NORTH' AND s.IsRenew = 1 AND YEAR (s.LastRenewDate) = YEAR (GETDATE()) ) AS ir, ( SELECT  COUNT (*) AS dcss_All_r FROM  t_smd_Store s WHERE  s.StoreType = 'DCSS' AND s.StoreStatus <> '关闭' AND s.IsRenew = 1 AND YEAR (s.LastRenewDate) = YEAR (GETDATE()) ) AS jr";
                if (year != DateTime.Now.Year)
                {
                    sql = sql.Replace("YEAR (GETDATE())", year.ToString(CultureInfo.InvariantCulture));
                }
                var db = clsCmm.clsDB.Query(sql).Rows[0];
                #region 没有记录就从数据库查出数据，来新建一条
                ddcn_East_complete.Value = db["ddcn_East"].ToString();
                ddcn_East_complete_r.Value = db["ddcn_East_r"].ToString();
                ddcn_East_complete_all.Value =
                    (Utils.StrToInt(db["ddcn_East"].ToString(), 0) + Utils.StrToInt(db["ddcn_East_r"].ToString(), 0))
                        .ToString(CultureInfo.InvariantCulture);
                ddcn_South_complete.Value = db["ddcn_South"].ToString();
                ddcn_South_complete_r.Value = db["ddcn_South_r"].ToString();
                ddcn_South_complete_all.Value =
                    (Utils.StrToInt(db["ddcn_South"].ToString(), 0) + Utils.StrToInt(db["ddcn_South_r"].ToString(), 0))
                        .ToString(CultureInfo.InvariantCulture);
                ddcn_West_complete.Value = db["ddcn_West"].ToString();
                ddcn_West_complete_r.Value = db["ddcn_West_r"].ToString();
                ddcn_West_complete_all.Value =
                    (Utils.StrToInt(db["ddcn_West"].ToString(), 0) + Utils.StrToInt(db["ddcn_West_r"].ToString(), 0))
                        .ToString(CultureInfo.InvariantCulture);
                ddcn_North_complete.Value = db["ddcn_North"].ToString();
                ddcn_North_complete_r.Value = db["ddcn_North_r"].ToString();
                ddcn_North_complete_all.Value =
                    (Utils.StrToInt(db["ddcn_North"].ToString(), 0) + Utils.StrToInt(db["ddcn_North_r"].ToString(), 0))
                        .ToString(CultureInfo.InvariantCulture);
                ddcn_All_complete.Value = db["ddcn_all"].ToString();
                ddcn_All_complete_r.Value = db["ddcn_all_r"].ToString();
                ddcn_All_complete_all.Value =
                    (Utils.StrToInt(db["ddcn_All"].ToString(), 0) + Utils.StrToInt(db["ddcn_All_r"].ToString(), 0))
                        .ToString(CultureInfo.InvariantCulture);

                dcss_East_complete.Value = db["dcss_East"].ToString();
                dcss_East_complete_r.Value = db["dcss_East_r"].ToString();
                dcss_East_complete_all.Value =
                    (Utils.StrToInt(db["dcss_East"].ToString(), 0) + Utils.StrToInt(db["dcss_East_r"].ToString(), 0))
                    .ToString(CultureInfo.InvariantCulture);
                dcss_South_complete.Value = db["dcss_South"].ToString();
                dcss_South_complete_r.Value = db["dcss_South_r"].ToString();
                dcss_South_complete_all.Value =
                   (Utils.StrToInt(db["dcss_South"].ToString(), 0) + Utils.StrToInt(db["dcss_South_r"].ToString(), 0))
                       .ToString(CultureInfo.InvariantCulture);
                dcss_West_complete.Value = db["dcss_West"].ToString();
                dcss_West_complete_r.Value = db["dcss_West_r"].ToString();
                dcss_West_complete_all.Value =
                    (Utils.StrToInt(db["dcss_West"].ToString(), 0) + Utils.StrToInt(db["dcss_West_r"].ToString(), 0))
                        .ToString(CultureInfo.InvariantCulture);
                dcss_North_complete.Value = db["dcss_North"].ToString();
                dcss_North_complete_r.Value = db["dcss_North_r"].ToString();
                dcss_North_complete_all.Value =
                    (Utils.StrToInt(db["dcss_North"].ToString(), 0) + Utils.StrToInt(db["dcss_North_r"].ToString(), 0))
                        .ToString(CultureInfo.InvariantCulture);
                dcss_All_complete.Value = db["dcss_all"].ToString();
                dcss_All_complete_r.Value = db["dcss_all_r"].ToString();
                dcss_All_complete_all.Value =
                   (Utils.StrToInt(db["dcss_All"].ToString(), 0) + Utils.StrToInt(db["dcss_All_r"].ToString(), 0))
                       .ToString(CultureInfo.InvariantCulture);
                #endregion
            }
            else
            {
                HasValue = true;
                var columns = _configService.GetList("ParentId = (select configid from t_smd_Config where code='XKFXH') order by seq").Tables[0].Rows;
                var rows = _configService.GetList("ParentId = (select configid from t_smd_Config where code='XKFXL') order by seq").Tables[0].Rows;
                for (var c = 0; c < columns.Count; c++)
                {
                    for (var r = 0; r < rows.Count; r++)
                    {
                        var contorl = Page.FindControl(rows[r]["name"] + "_" + columns[c]["name"]);
                        if (contorl is HtmlInputText)
                        {
                            var htmlInputText = contorl as HtmlInputText;
                            htmlInputText.Value = summarys.Select("rowName='" + rows[r]["name"] + "' and columnName = '" + columns[c]["name"] + "'")[0]["Value"].ToString();
                        }
                    }
                }

            }


        }

        protected void SaveSummary()
        {
            (t_smd_Summary._Year == Utils.StrToInt(yearSearch.Value, DateTime.Now.Year) & t_smd_Summary._ReportType == "main").Delete<t_smd_Summary>(
                Storage.Context3);
            var summarys = new List<t_smd_Summary>();
            var columns = _configService.GetList("ParentId = (select configid from t_smd_Config where code='XKFXH') order by seq").Tables[0].Rows;
            var rows = _configService.GetList("ParentId = (select configid from t_smd_Config where code='XKFXL') order by seq").Tables[0].Rows;
            for (var c = 0; c < columns.Count; c++)
            {
                for (var r = 0; r < rows.Count; r++)
                {
                    var summary = new t_smd_Summary();
                    summary.Year = Utils.StrToInt(yearSearch.Value, DateTime.Now.Year);
                    summary.RowName = rows[r]["name"].ToString();
                    summary.ColumnName = columns[c]["name"].ToString();
                    summary.ReportType = "main";
                    summary.Value = Utils.StrToInt(AksRequest.GetFormString(rows[r]["name"] + "_" + columns[c]["name"]), 0);
                    summarys.Add(summary);
                }
            }

            // ReSharper disable once CoVariantArrayConversion
            Storage.Save(Storage.Context3, summarys.ToArray());
            SuccessMsg();
        }

        protected void Export()
        {
            var ms = new MemoryStream();

            var year = Utils.StrToInt(AksRequest.GetFormString("year"), DateTime.Now.Year);
            clsCmm.clsDB.Storage("smdDB");
            var summarys = clsCmm.clsDB.Query("select * from t_smd_Summary where year=" + year + " and ReportType='main'");
            using (summarys)
            {
                HSSFWorkbook hssfworkbook;

                using (
                    var file = new FileStream(Server.MapPath("~/") + "Pages\\SMD\\Excel\\NewAndUdate.xls",
                        FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
                
                var sheet = hssfworkbook.GetSheetAt(0);

                var columns =
                    _configService.GetList(
                        "ParentId = (select configid from t_smd_Config where code='XKFXH') order by seq").Tables[0].Rows;
                var rows =
                    _configService.GetList(
                        "ParentId = (select configid from t_smd_Config where code='XKFXL') order by seq").Tables[0].Rows;

                for (var r = 0; r < rows.Count; r++)
                {
                    for (var c = 0; c < columns.Count; c++)
                    {
                        sheet.GetRow(2 + r)
                            .GetCell(2 + c)
                            .SetCellValue(
                                summarys.Select("rowName='" + rows[r]["name"] + "' and columnName = '" +
                                                columns[c]["name"] + "'")[0]["Value"].ToString());
                    }
                }


                hssfworkbook.Write(ms);//
                Response.AddHeader("Content-Disposition", string.Format("attachment; filename=NewAndUpdate"+year+".xls"));
                Response.BinaryWrite(ms.ToArray());
                ms.Close();
                ms.Dispose();
                
                

            }
        }

    }
}